﻿/*
 * Cratos Desktop
 * Cratos Persistence class
 * 
 * Copyright (c) 2011-2012 Draco
 * 
 * This file is part of Cratos.
 * 
 * Cratos is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * Cratos is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Cratos.  If not, see <http://www.gnu.org/licenses/>.
 * 
 *       .==.        .==.
 *      //'^\\      //^'\\
 *     // ^ ^\(\__/)/^ ^^\\
 *    //^ ^^ ^/6  6\ ^^ ^ \\
 *   //^ ^^ ^/( .. )\^ ^ ^ \\
 *  // ^^ ^/\| V""V|/\^ ^ ^ \\
 * //^^/\/ /  '~~'  \ \/\^ ^ \\
 *  ------------------------------------
 *  HERE BE DRAGONS
 *  
 *  Johan Dercksen - johan@draco.co.za
 */
using System;
using System.IO;
using System.Text;
using System.Linq;
using System.Data;
using System.Reflection;
using System.IO.Compression;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Runtime.Serialization.Formatters.Binary;

using Cratos.Tools.General;
using Cratos.Tools.DataAccess;

namespace Cratos.Desktop.Persistence
{
	/// <summary>
	/// Cratos Persistence class
	/// </summary>
	public class CratosPersistence
	{
		#region Class Constant declarations
		#endregion

		#region Class Event declarations
		#endregion

		#region Class Variable declarations
		private SqlCeEngine ceEngine;
		private string			sqlTableCreate;
		#endregion

		#region Class Constructors & Destructors
		/// <summary>
		/// Class Constructor
		/// </summary>
		public CratosPersistence()
		{
			// Create and Setup the Database object
			this.DBSqlString              = new CratosDBSqlString();
			this.DBSqlString.DatabaseType = CratosDatabaseType.SqlCeServer;
			this.DBSqlString.DBActionType = DOCratosActionType.SqlString;
		}
		#endregion

		#region Class Properties
		/// <summary>
		/// Database Filename
		/// </summary>
		public string DBFilename { get; set; }

		/// <summary>
		/// Database Connection String
		/// </summary>
		public string DBConnectionString
		{
			get { return (this.DBSqlString.ConnectionString); }
			set { this.DBSqlString.ConnectionString = value; }
		}

		/// <summary>
		/// Cratos Database Access - SQL String
		/// </summary>
		public CratosDBSqlString DBSqlString { get; internal set; }
		#endregion

		#region Class Methods
		#region Database Methods
		/// <summary>
		/// DB Create Database
		/// </summary>
		/// <returns>A boolean indicating the success of the method</returns>
		public bool DBCreateDatabase(bool dbOverwrite)
		{
			// Declare the required variables
			bool returnValue = false;

			// Make sure we have a Connection String
			if (string.IsNullOrEmpty(this.DBConnectionString))
			{
				// Throw a new exception
				throw new ArgumentNullException("DBConnectionString");
			}
			else if (string.IsNullOrEmpty(this.DBFilename))
			{
				// Throw a new exception
				throw new ArgumentNullException("DBFilename");
			}
			else
			{
				// Delete any existing DB if required
				if (File.Exists(this.DBFilename) && dbOverwrite)
				{
					// Delete the DB File if it exists
					File.Delete(this.DBFilename);

					// Create the Database
					this.ceEngine = new SqlCeEngine(this.DBConnectionString);
					this.ceEngine.CreateDatabase();
				}
				else
				{
					// Create the Database
					this.ceEngine = new SqlCeEngine(this.DBConnectionString);
					this.ceEngine.CreateDatabase();
				}
			}

			// Return to calling method
			return (returnValue);
		}

		/// <summary>
		/// Database Table Exists
		/// </summary>
		/// <param name="tableName">Table Name</param>
		/// <returns>A boolean that indicates whether the relevant table exists in the DB</returns>
		public bool DBTableExists(string tableName)
		{
			// Declare the required variables
			bool	 returnValue = false;
			string sqlQuery;

			// Connect to the Database
			if (this.DBSqlString.Connect())
			{
				// Setup the SQL Query
				sqlQuery = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);

				// Execute the Query
				this.DBSqlString.DBActionText = sqlQuery;
				this.DBSqlString.DBActionType = DOCratosActionType.SqlString;

				returnValue = (this.DBSqlString.ExecuteScalar() != null);

				// Disconnect
				this.DBSqlString.Disconnect();
			}

			// Return to calling method
			return (returnValue);
		}

		/// <summary>
		/// Database Create Paging Table
		/// </summary>
		/// <returns>A boolean indicating the success of the method</returns>
		public bool DBPagingTableCreate()
		{
			// Declare the required variables
			bool returnValue = false;

			// Connect to the DB
			if (this.DBSqlString.Connect())
			{
				// Setup the DB SQL String object
				this.DBSqlString.DBActionText = this.sqlTableCreate;
				this.DBSqlString.DBActionType = DOCratosActionType.SqlString;

				// Execute the relevant SQL Query
				this.DBSqlString.ExecuteNonQuery();

				// Disconnect from the DB
				this.DBSqlString.Disconnect();

				// Set the successful return value
				returnValue = true;
			}

			// Return to calling method
			return (returnValue);
		}
		#endregion

		#region Normal Collection Persistence
		/// <summary>
		/// Database Add Collection
		/// </summary>
		/// <param name="doCollection">Data Object Collection</param>
		/// <returns>A Boolean indicating the success of the method</returns>
		public bool DBAddCollection(IDOCratosCollectionBase doCollection) 
		{
			// Declare the required variables
			bool returnValue = false;

			// Return to calling method
			return(returnValue);
		}

		/// <summary>
		/// Database Retrieve Collection
		/// </summary>
		/// <param name="doGuid">Data Object GUID</param>
		/// <returns>The specified DO Collection</returns>
		public IDOCratosCollectionBase DBRetrieveCollection(Guid doGuid)
		{
			// Declare the required variables
			IDOCratosCollectionBase returnValue = null;

			// Return to calling method
			return (returnValue);
		}
		#endregion

		#region Paging Collection Persistence
		/// <summary>
		/// Database Add Collection
		/// </summary>
		/// <param name="pageNo">Page Number</param>
		/// <param name="doGuid">Data Object GUID</param>
		/// <param name="doCollection">Data Object Collection</param>
		/// <param name="allProperties">Data Object Properties</param>
		/// <returns>A Boolean indicating the success of the method</returns>
		public bool DBAddPagingCollection(long pageNo, Guid doGuid,  Collection<IDOCratos> doCollection, Collection<PropertyInfo> allProperties)
		{
			// Declare the required variables
			bool									returnValue = false;
			//int										propOrdinal;
			string								tableName;
			SqlCeResultSet				resultSet;
			SqlCeUpdatableRecord	sqlRecord;

			// Create the Table Name
			tableName = string.Format("{0}_Page_{1}", doGuid.ToString(), pageNo.ToString());

			// Build the relevant SQL Statement(s)
			this.BuildSql(tableName, doGuid, allProperties);

			// Determine if the DB Table exists.
			if (!this.DBTableExists(tableName))
			{
				// Create the DB Table
				if (!this.DBPagingTableCreate()) { return (false); }
			}

			// Connect to the DB
			if (this.DBSqlString.Connect())
			{
				// Update the SQL Command
				this.DBSqlString.Command.CommandText = tableName;
				this.DBSqlString.Command.CommandType = CommandType.TableDirect;

				// Create and Setup the Resultset and Updatable record
				resultSet = ((SqlCeCommand)this.DBSqlString.Command).ExecuteResultSet(ResultSetOptions.Updatable);
				sqlRecord = resultSet.CreateRecord();
				
			  // Process all the collection objects
			  foreach (IDOCratos currentObject in doCollection)
			  {
					// Reset the Property Ordinal
					//propOrdinal = 0;

					// Process all the Properties
					foreach (PropertyInfo currentProperty in allProperties) 
					{
						if (this.IsTypeNullable(currentProperty.PropertyType))
						{
							// Retrieve the underlying Type
							//inType = Nullable.GetUnderlyingType(inType);
						}
						else if (currentProperty.PropertyType.IsEnum)
						{
							//inType = typeof(string);
						}
						else if (currentProperty.PropertyType == typeof(string))
						{
							sqlRecord.SetValue(sqlRecord.GetOrdinal(currentProperty.Name), currentProperty.GetValue(currentObject, null));
						}
						else if (currentProperty.PropertyType == typeof(DateTime))
						{
							DateTime tempDate = (DateTime)currentProperty.GetValue(currentObject, null);
							sqlRecord.SetValue(sqlRecord.GetOrdinal(currentProperty.Name), 
																 string.Format("D: {0}", tempDate.ToString("ddMMyyyyHH:mm:ss")));
						}
						else if (!currentProperty.PropertyType.IsValueType && currentProperty.PropertyType.IsClass)
						{
							//return (SqlDbType.VarBinary);
						}
						else
						{
							sqlRecord.SetValue(sqlRecord.GetOrdinal(currentProperty.Name), currentProperty.GetValue(currentObject, null));
						}
					}

					// Insert the SQL record
					resultSet.Insert(sqlRecord);
				}

				// Close the connection
				this.DBSqlString.Disconnect();
			}

			// Return to calling method
			return (returnValue);
		}

		/// <summary>
		/// Database Retrieve Collection
		/// </summary>
		/// <param name="doGuid">Data Object GUID</param>
		/// <param name="pageNo">Page Number</param>
		/// <returns>The specified DO Collection</returns>
		public Collection<T> DBRetrievePagingCollection<T>(Guid doGuid, long pageNo) where T : IDOCratos, new()
		{
			// Declare the required variables
			T							newDO;
			string				tableName;
			Collection<T>	returnValue = null;

			// Create the Table Name
			tableName = string.Format("{0}_Page_{1}", doGuid.ToString(), pageNo.ToString());

			// Create the return Data Collection object
			returnValue = new Collection<T>();

			// Connect to the DB
			if (this.DBSqlString.Connect())
			{
				// Update the SQL Command
				this.DBSqlString.DBActionText = string.Format("SELECT * FROM [{0}]", tableName);
				this.DBSqlString.DBActionType = DOCratosActionType.SqlString;

				// Execute the Query
				this.DBSqlString.ExecuteReader();

				// Process all the returned data
				while (this.DBSqlString.DataReader.Read())
				{
					// Create and Setup the new DO
					newDO = new T();

					// Load the data from the DB Reader
					this.DBLoadDataFromReader(newDO);

					// Add the item to the return data collection
					returnValue.Add(newDO);
				}

				// Close the connection
				this.DBSqlString.Disconnect();
			}

			// Return to calling method
			return (returnValue);
		}
		#endregion
		#endregion

		#region Class Support Methods
		private SqlDbType GetSqlDBTypeFromType(Type inType)
		{
			// Declare the required variables
			DbType				dbType;
			TypeConverter	typeConverter;
			SqlCeParameter	sqlParam;

			try 
			{
				// Determine if we are working with a Nullable Type
				if (this.IsTypeNullable(inType)) 
				{
					// Retrieve the underlying Type
					inType = Nullable.GetUnderlyingType(inType);
				}
				else if (inType.IsEnum) { inType = typeof(string); }
				else if (inType == typeof(string)) { }
				else if (inType == typeof(DateTime)) { inType = typeof(string); }
				else if (!inType.IsValueType && inType.IsClass) { return (SqlDbType.VarBinary); }

				// Retrieve the Type Converter
				typeConverter = TypeDescriptor.GetConverter(typeof(DbType));

				// Convert the relevant Type
				dbType = (DbType)typeConverter.ConvertFrom(inType.Name);

				// The SQL parameter class knows how to map between DbType and SqlDBType. 
				sqlParam        = new SqlCeParameter();
				sqlParam.DbType = dbType;

				// Return to calling method
				return (sqlParam.SqlDbType);
			}
			catch (Exception)
			{
				throw new Exception("Cannot get Sql Db Type from: " + inType.Name);
			}
		}

		private string GetSqlServerCETypeName(SqlDbType dbType)
		{
			// Declare the required variables
			string	returnValue = string.Empty;

			// Handle the relevant DB Type
			switch (dbType)
			{
				case SqlDbType.Binary: 
					returnValue = string.Format("binary ({0})", 4096); 
					break;

				case SqlDbType.Char:
					returnValue = string.Format("nchar({0})", 2048);
					break;

				case SqlDbType.NVarChar:
				case SqlDbType.VarChar:
					returnValue = string.Format("nvarchar({0})", 1024);
					break;

				default:
					returnValue = dbType.ToString().ToLower();
					break;
			}

			// Return to calling method
			return(returnValue);
		}

		private void BuildSql(string tableName, Guid doGuid, Collection<PropertyInfo> allProperties)
		{
			// Declare the required variables
			SqlDbType			sqlDbType;
			StringBuilder sqlTableCreate;

			// Create and Setup the SQL string
			sqlTableCreate = new StringBuilder();
			sqlTableCreate.AppendFormat("CREATE TABLE [{0}] (", tableName);

			// Process all the Properties
			foreach (PropertyInfo currentProperty in allProperties)
			{
				// Retrieve the SQL DB Type
				sqlDbType = this.GetSqlDBTypeFromType(currentProperty.PropertyType);

				// Add the relevant data to the SQL String
				sqlTableCreate.AppendFormat("{0} {1}, ", currentProperty.Name, this.GetSqlServerCETypeName(sqlDbType));
			}

			sqlTableCreate.Length = sqlTableCreate.Length - 2;
			sqlTableCreate.Append(")\n");

			// Update the relevant values
			this.sqlTableCreate = sqlTableCreate.ToString();

			// Return to calling method
			return;
		}

		#region Data Loading methods
		/// <summary>
		/// Load Data from the Data Reader
		/// </summary>
		private void DBLoadDataFromReader(IDOCratos cratosDO)
		{
			// Declare the required variables

			// Process all the Data Object columns
			foreach (PropertyInfo currentProperty in cratosDO.AllDOProperties)
			{
				// Make sure the column exists in the data row
				if (this.DBSqlString.ReaderContainsField(currentProperty.Name))
				{
					// Make sure the field contains a value
					if (this.DBSqlString.ReaderIsFieldNull(currentProperty.Name))
					{
						// Set the property to its default value
						this.DBSetDOPropertyNull(cratosDO, currentProperty.Name, currentProperty.PropertyType);
					}
					else
					{
						// Set the property value
						currentProperty.SetValue(cratosDO, this.DBSqlString.ReaderGetValue(currentProperty.Name), null);
					}
				}
			}

			// Set the loaded indicator
			cratosDO.IsLoaded = true;

			// Return to calling method
			return;
		}

		private void DBSetDOPropertyNull(IDOCratos doCratos, string propertyName, Type doType)
		{
			// Declare the required variables

			// Handle the relevant Data Type
			switch (doType.Name)
			{
				case "System.Decimal":
					// Set the default null value
					doCratos.GetType().GetProperty(propertyName).SetValue(doCratos, 0.0m, null);
					break;

				case "System.Boolean":
					// Set the default null value
					doCratos.GetType().GetProperty(propertyName).SetValue(doCratos, false, null);
					break;

				case "System.DateTime":
					// Set the default null value
					doCratos.GetType().GetProperty(propertyName).SetValue(doCratos, new DateTime(1753, 1, 1), null);
					break;

				case "System.String":
					// Set the default null value
					doCratos.GetType().GetProperty(propertyName).SetValue(doCratos, string.Empty, null);
					break;

				default:
					// Set the default null value
					doCratos.GetType().GetProperty(propertyName).SetValue(doCratos, null, null);
					break;
			}

			// Return to calling method
			return;
		}
		#endregion

		private bool IsTypeNullable(Type typeToTest)
		{
			// Declare the required variables

			// Abort if no type supplied 
			if (typeToTest == null) { return (false); }

			// Report whether TypeToTest is a form of the Nullable<> type 
			return (typeToTest.IsGenericType && (typeToTest.GetGenericTypeDefinition() == typeof(Nullable<>)));
		}
		#endregion
	}
}
